from flask import Flask,Response,request
from db import get_db
import json

app = Flask(__name__)

@app.route('/')
def index():
    return "hello mysql"

@app.route('/query')
def query_data():
    db = get_db() # 获取数据库连接(有了数据库连接之后flask有能力对mysql进行访问)
    try:
        # 开始对数据库进行操作
        with db.cursor() as cursor:
            # 执行查询
            sql = "SELECT * FROM users"
            cursor.execute(sql)  # 使用参数化查询防止SQL注入
            results = cursor.fetchall()
            print(results)
            return Response(
                json.dumps(results, ensure_ascii=False),
                content_type='application/json; charset=utf-8'
            )
    finally:
        db.close() # 不管对数据库的操作有没有成功,数据库都要关闭!

@app.route('/insert', methods=['POST'])
def insert_data():
    # 获取post请求携带的数据
    username = request.form.get('username')
    email = request.form.get('email')
    db = get_db()
    try:
        with db.cursor() as cursor:
            # 插入数据
            sql = "INSERT INTO users (username, email) VALUES (%s, %s)"
            cursor.execute(sql, (username, email))
            db.commit()  # 提交事务
            return {'message': 'Insert successful'}
    except Exception as e:
        db.rollback()  # 出错时回滚
        return {'error': str(e)}, 500
    finally:
        db.close()

if __name__ == '__main__':
    app.run(debug=True)